import pymysql
from pymysql.cursors import Cursor
from pymysql.converters import escape_string

host = 'localhost'
dbname = 'parkadmin'
user = 'parkadmin'
password = 'pxchtfTPa3i3rnp6'


class Database:

    def __init__(self):
        self.conn = None
        self.cursor: Cursor = None

    def __enter__(self):
        self.conn = self.connect()
        self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        return self

    def __exit__(self, exc_type, exc_value, traceback):
        self.close()

    def execute(self, sql, args=None):
        if not args:
            args = None
        self.cursor.execute(sql,args)

    def connect(self):
        return pymysql.connect(host=host,
                               user=user,
                               password=password,
                               database=dbname)

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
            
    def build_dict(self, obj):
        if isinstance(obj, dict):
            dt = obj
        else:
            dt = obj.__dict__
        ignore_fields = ['id', 'gmt_create', 'gmt_modified']
        new_dt = {}
        for k,v in dt.items():
            if k not in ignore_fields:
                new_dt[k] = v
        return new_dt

    def get_insert_sql_from_obj(self, obj, table_name):

        sql = f'INSERT INTO {table_name} ('

        dt = self.build_dict(obj)

        for key in dt.keys():
            sql += f'{key},'

        sql = sql[0:-1] + ') VALUES ('

        for value in dt.values():
            if isinstance(value, str):
                sql += f'"{escape_string(value)}",'
            else:
                sql += f'"{value}",'

        sql = sql[0:-1] + ')'

        return sql

    def get_update_sql_from_obj(self, id, obj, table_name):

        sql = f'UPDATE {table_name} SET '

        dt = self.build_dict(obj)

        for k, v in dt.items():
            sql += f'`{k}` = '
            if isinstance(v, str):
                sql += f'"{escape_string(v)}",'
            else:
                sql += f'"{v}",'

        sql = sql.strip(',')

        sql += f' WHERE `id` = "{id}"'

        return sql
